package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.Grade;
import com.xmy.cultivate.entity.Staff;
import com.xmy.cultivate.entity.StudentGrade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.response.ArrivalRate;
import com.xmy.cultivate.entity.views.ShiftList;
import com.xmy.cultivate.entity.views.StudyPlan;
import com.xmy.cultivate.respons.NotScheduled;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * <p>
 * 学生所在班级与课程 Mapper 接口
 * </p>
 *
 * @author min
 * @since 2022-08-09
 */
@Mapper
public interface StudentGradeMapper extends BaseMapper<StudentGrade> {


    /**
     * 获取最后一次分班数据
     *
     * @return
     */
    @Select("select * from student_grade where school_id =#{schoolId} and subjects_id = #{subjectsId} and student_id = #{studentId} and year_part=#{yearPart} and quarter_num=#{quarterNum} and deleted=0 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForStudent(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);


    /**
     * 获取最后一次分班数据
     *
     * @return
     */
    @Select("select * from student_grade where order_detail_id = #{orderDetailId} and deleted=0 and reading_status<>3 and status=1 order by created_at desc limit 0,1 ")
    public abstract StudentGrade getLastForOrderDetailId(@Param("orderDetailId") Long orderDetailId);


    @Select("select a.id,a.reading_status,getCourseNameForId(b.course_id) as courseName,getSchoolName(a.school_id) as schoolName,b.name as gradeName,getStaffNameForId(b.teacher_id) as teacherName,c.`name` as studentName,c.linkman,c.linkman_phone from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id left JOIN student c on a.student_id = c.id ${ew.customSqlSegment}")
    public abstract IPage<ShiftList> findAll(IPage<?> page, @Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select count(id) as count from student_grade where student_id = #{studentId} and grade_id = #{gradeId} and status = 1 and reading_status = 1")
    public abstract Integer studentIsAtGrade(@Param("studentId") Long studentId, @Param("gradeId") Long gradeId);

    @Select("select count(DISTINCT student_id) as count from student_grade where school_id = #{schoolId} and grade_id = #{gradeId} and status = 1 and reading_status = 1 and deleted=0")
    public abstract Integer studentCount(@Param("schoolId") Long schoolId, @Param("gradeId") Long gradeId);

    @Select("select name from (select DISTINCT c.id,c.name from student_grade as a LEFT JOIN grade as b on a.grade_id = b.id LEFT JOIN staff as c on b.teacher_id = c.id where a.school_id =#{schoolId} and a.student_id =#{studentId} and a.subjects_id=#{subjectsId} and a.year_part = #{yearPart}  and a.quarter_num = #{quarterNum} and a.`status`=1 and a.reading_status not in(2,3)) as t")
    public abstract List<String> getGradeTeacherNameList(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum);

    @Select("select * from student_grade ${ew.customSqlSegment}")
    public abstract List<StudentGrade> selectByGrade(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select id from grade ${ew.customSqlsegment}")
    public abstract List<String> ListGradeId(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select student_id from student_grade ${ew.customSqlsegment}")
    public abstract List<String> ListStudentId(@Param(Constants.WRAPPER) Wrapper wrapper);

    @Select("select grade_id from student_grade where student_id = #{student_id} and reading_status = 1 group by student_id")
    public abstract String getGradeId(@Param("student_id") String studentId);

    @Select("select count(DISTINCT student_id) as count from student_grade ${ew.customSqlsegment}")
    public abstract Integer studentCountQuery(@Param(Constants.WRAPPER) Wrapper wrapper);

    /**
     * 分组获取最新一条数据
     *
     * @param queryWrapper
     * @return
     */
    //@Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade where year_part = 2023 and quarter_num = 2 GROUP BY student_id,subjects_id)  as t2 where t1.id = t2.id")
    @Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade ${ew.customSqlSegment})  as t2 where t1.id = t2.id")
    public abstract List<StudentGrade> getGroupByAtNewList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    /**
     * 分组获取最新一条数据
     *
     * @param queryWrapper
     * @return
     */
    //@Select("select t1.* from student_grade as t1,(select max(id) as id from student_grade where year_part = 2023 and quarter_num = 2 GROUP BY student_id,subjects_id)  as t2 where t1.id = t2.id")
    @Select("select t1.student_id from student_grade as t1,(select max(id) as id from student_grade_view ${ew.customSqlSegment})  as t2 where t1.id = t2.id")
    public abstract List<Long> getGroupByAtNewStudentIdList(@Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    @Select("select * from student_grade as t1,(select max(id) as id from student_grade_view ${ew.customSqlSegment})  as t2 where t1.id = t2.id")
    public abstract IPage<StudyPlan> getGroupByAtNewStudentList(IPage<?> page, @Param(Constants.WRAPPER) QueryWrapper queryWrapper);

    /**
     * 在读学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,",
            "(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4",
            "where",
            "use_course_count &gt; 0 and residue_course_count &gt;0 and reading_status_count&gt;0 and stop_status_count=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer readingStudentCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);

    /**
     * 结课学员数量
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count",
            "from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where ((residue_course_count = 0 and reading_status_count = 0 and stop_status_count=0 and is_out=0 and is_turn_out=0) or (residue_course_count > 0 and use_course_count &gt; 0 and reading_status_count = 0 and stop_status_count=0))",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer countStudentByStatusJoint(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 异常学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count",
            "from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where use_course_count = 0 and residue_course_count&gt;0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer abnormalStudentCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 退费学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count",
            "from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where residue_course_count=0 and is_out&gt;0 and is_turn_out=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer returnStudentCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 停课学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count",
            "from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where use_course_count&gt;0 and residue_course_count&gt;0 and stop_status_count&gt;0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer stopStudnetCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 转校学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as count from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,SUM(use_course_count) as use_course_count,SUM(residue_course_count) as residue_course_count,SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,(select SUM(case reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count",
            "from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as stop_status_count,(select SUM(case reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count from student_grade as t2 where t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0 ORDER BY id desc limit 1) as reading_status_count",
            "from order_detail as t1",
            "where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt;2 and deleted = 0",
            "GROUP BY year_part,quarter_num,school_id,subjects_id,student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where  is_turn_out&gt;0 and residue_course_count = 0 and is_out=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "</script>"})
    public abstract Integer turnOutStudentCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);

    //===========================================================

    /**
     * 在读学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as yearClassId,school_id AS studentId from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select t1.id,t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id,SUM(t1.use_course_count) as use_course_count,SUM(t1.residue_course_count) as residue_course_count,",
            "SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "SUM(case t2.reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count,",
            "SUM(case t2.reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count",
            "from order_detail as t1",
            "left join student_grade as t2 on t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and",
            "t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0",
            "where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.`status`&lt;&gt;2 and t1.deleted = 0",
            "GROUP BY t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4",
            "where",
            "use_course_count &gt; 0 and residue_course_count &gt;0 and reading_status_count&gt;0 and stop_status_count=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` in (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "GROUP BY school_id",
            "ORDER BY yearClassId DESC",
            "</script>"})
    public abstract List<StudentGrade> readingStudentList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);

    /**
     * 结课学员数量
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as yearClassId,school_id AS studentId from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select t1.id,t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id,SUM(t1.use_course_count) as use_course_count,SUM(t1.residue_course_count) as residue_course_count,",
            "SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "SUM(case t2.reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count,",
            "SUM(case t2.reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count",
            "from order_detail as t1",
            "left join student_grade as t2 on t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and",
            "t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0",
            "where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.`status`&lt;&gt;2 and t1.deleted = 0",
            "GROUP BY t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where ((residue_course_count = 0 and reading_status_count = 0 and stop_status_count=0 and is_out=0 and is_turn_out=0) or (residue_course_count > 0 and use_course_count &gt; 0 and reading_status_count = 0 and stop_status_count=0))",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` in (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "GROUP BY school_id",
            "ORDER BY yearClassId DESC",
            "</script>"})
    public abstract List<StudentGrade> countStudentByStatusList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 异常学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as yearClassId,school_id AS studentId from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select t1.id,t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id,SUM(t1.use_course_count) as use_course_count,SUM(t1.residue_course_count) as residue_course_count,",
            "SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "SUM(case t2.reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count,",
            "SUM(case t2.reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count",
            "from order_detail as t1",
            "left join student_grade as t2 on t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and",
            "t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0",
            "where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.`status`&lt;&gt;2 and t1.deleted = 0",
            "GROUP BY t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where use_course_count = 0 and residue_course_count&gt;0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` in (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "GROUP BY school_id",
            "ORDER BY yearClassId DESC",
            "</script>"})
    public abstract List<StudentGrade> abnormalStudentList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 退费学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as yearClassId,school_id AS studentId from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select t1.id,t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id,SUM(t1.use_course_count) as use_course_count,SUM(t1.residue_course_count) as residue_course_count,",
            "SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "SUM(case t2.reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count,",
            "SUM(case t2.reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count",
            "from order_detail as t1",
            "left join student_grade as t2 on t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and",
            "t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0",
            "where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.`status`&lt;&gt;2 and t1.deleted = 0",
            "GROUP BY t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where residue_course_count=0 and is_out&gt;0 and is_turn_out=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` in (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "GROUP BY school_id",
            "ORDER BY yearClassId DESC",
            "</script>"})
    public abstract List<StudentGrade> returnStudentList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 停课学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as yearClassId,school_id AS studentId from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select t1.id,t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id,SUM(t1.use_course_count) as use_course_count,SUM(t1.residue_course_count) as residue_course_count,",
            "SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "SUM(case t2.reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count,",
            "SUM(case t2.reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count",
            "from order_detail as t1",
            "left join student_grade as t2 on t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and",
            "t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0",
            "where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.`status`&lt;&gt;2 and t1.deleted = 0",
            "GROUP BY t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where use_course_count&gt;0 and residue_course_count&gt;0 and stop_status_count&gt;0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` in (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "GROUP BY school_id",
            "ORDER BY yearClassId DESC",
            "</script>"})
    public abstract List<StudentGrade> stopStudnetList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    /**
     * 转校学员
     *
     * @param yearPart
     * @param quarterNum
     * @param schoolId
     * @param subjectsId
     * @return
     */
    @Select({"<script>",
            "select count(id) as yearClassId,school_id AS studentId from (",
            "select id,year_part,quarter_num,school_id,subjects_id,student_id,use_course_count,residue_course_count,IFNULL(stop_status_count,0) as stop_status_count,IFNULL(reading_status_count,0) as reading_status_count,is_out,is_turn_out",
            "from (",
            "select t1.id,t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id,SUM(t1.use_course_count) as use_course_count,SUM(t1.residue_course_count) as residue_course_count,",
            "SUM(CASE WHEN t1.out_house&gt;0 THEN 1 ELSE 0 END) as is_out,SUM(CASE WHEN t1.turn_out_house&gt;0 THEN 1 ELSE 0 END) as is_turn_out,",
            "SUM(case t2.reading_status WHEN 4 THEN 1 ELSE 0 END) as stop_status_count,",
            "SUM(case t2.reading_status WHEN 1 THEN 1 ELSE 0 END) as reading_status_count",
            "from order_detail as t1",
            "left join student_grade as t2 on t1.year_part = t2.year_part and t1.quarter_num = t2.quarter_num and t1.school_id=t2.school_id and t1.subjects_id = t2.subjects_id and",
            "t1.student_id = t2.student_id and t2.`status`&lt;&gt;3 and t2.deleted=0",
            "where t1.year_part = ${yearPart} and t1.quarter_num = ${quarterNum} and t1.`status`&lt;&gt;2 and t1.deleted = 0",
            "GROUP BY t1.year_part,t1.quarter_num,t1.school_id,t1.subjects_id,t1.student_id",
            ") as t3 GROUP BY school_id,subjects_id,student_id",
            ") as t4 where  is_turn_out&gt;0 and residue_course_count = 0 and is_out=0",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "and `school_id` in (${schoolId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and `subjects_id` = ${subjectsId}",
            "</when>",
            "GROUP BY school_id",
            "ORDER BY yearClassId DESC",
            "</script>"})
    public abstract List<StudentGrade> turnOutStudentList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("schoolId") String schoolId, @Param("subjectsId") String subjectsId);


    //停课学员
    @Select({"<script>",
            "SELECT",
            "count(*) as count ",
            "FROM",
            "(",
            "SELECT",
            "t.*,",
            "getSchoolName ( t.school_id ) AS school_name,",
            "t1.`name` AS student_name,",
            "getSubjectsName ( t.subjects_id ) AS subjects_name,",
            "t2.grade_id ",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND use_course_count &gt; 0 AND residue_course_count &gt; 0 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter_num` = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND lesson_type = ${lessonType} ",
            "AND is_last = 1 ",
            "AND reading_status = 4 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "student_id,",
            "lesson_type ",
            ") AS t2 ON t.school_id = t2.school_id ",
            "AND t.subjects_id = t2.subjects_id ",
            "AND t.student_id = t2.student_id ",
            "AND t.lesson_type = t2.lesson_type ",
            "AND t2.reading_status = 4 ",
            "INNER JOIN student AS t1 ON t.student_id = t1.id ",
            "WHERE",
            "t.residue_course_count &gt; 0 ",
            "AND t.use_course_count &gt; 0 ",
            "AND t.turn_out_house = 0 ",
            ") AS tt",
            "LEFT JOIN grade AS g ",
            "on",
            "tt.grade_id = g.id",
            "where 1=1",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and tt.school_id =${schoolId}",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "AND tt.`lesson_type` = ${lessonType} ",
            "</when>",
            "<if test= 'type == 1'>",
            "AND g.teacher_id = ${teacherId}",
            "</if>",
            "<if test= 'type == 2'>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and tt.school_id =${schoolId}",
            "</when>",
            "</if>",
            "<if test= 'type == 3'>",
            "and g.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<if test= 'type == 4'>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and tt.school_id =${schoolId}",
            "</when>",
            "</if>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and tt.subjects_id =${subjectsId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "AND tt.`grade_id` = ${gradeId} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND g.`teacher_id` = ${teacherId} ",
            "</when>",
            "</script>"})
    public abstract Integer DiscontinuedStudentsCount(@Param("yearPart") String yearPart,
                                                      @Param("quarterNum") String quarterNum,
                                                      @Param("schoolId") String schoolId,
                                                      @Param("teacherId") String teacherId,
                                                      @Param("gradeId") String gradeId,
                                                      @Param("subjectsId") String subjectsId,
                                                      @Param("startDate") String startDate,
                                                      @Param("endDate") String endDate,
                                                      @Param("type") String type,
                                                      @Param("lessonType") String lessonType);//@Param("schoolId") Long schoolId, @Param("studentId") String studentId "GROUP_CONCAT(student_id) as suspendClassesCount",

    //在校学员
    @Select({"<script>",
            "SELECT",
            "*,",
            "getStaffNameForId(teacher_id) as teacher_name,",
            "g.`name` AS grade_name,",
            "sum( CASE WHEN residue_course_count = 0 AND use_course_count &gt; 0 AND turn_out_house = 0 THEN 1 ELSE 0 END ) AS losse_student_count,",
            "sum( CASE WHEN residue_course_count > 0 AND use_course_count > 0 AND turn_out_house = 0 and reading_status=4 THEN 1 ELSE 0 END ) AS suspend_classes_count,",
            "sum( CASE WHEN use_course_count &gt; 0 THEN 1 ELSE 0 END ) AS student_all_count",
            "FROM",
            "(",
            "SELECT",
            "t.*,",
            "getSchoolName ( t.school_id ) AS school_name,",
            "t1.`name` AS student_name,",
            "getSubjectsName ( t.subjects_id ) AS subjects_name,",
            "t2.grade_id,",
            "t2. year_class_id,",
            "t2.reading_status",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house ",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history",
            "</when>",
            "WHERE",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter_num` = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND lesson_type = ${lessonType} ",
            "AND is_last = 1 ",
            "and status =1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON t.school_id = t2.school_id ",
            "AND t.subjects_id = t2.subjects_id ",
            "AND t.student_id = t2.student_id ",
            "AND t.lesson_type = t2.lesson_type",
            "INNER JOIN student AS t1 ON t.student_id = t1.id ",
            "WHERE",
            "1=1",
            ") AS tt",
            "LEFT JOIN grade AS g ",
            "on",
            "tt.grade_id = g.id",
            "where 1=1",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "and tt.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "and course_type_id in (${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and tt.subjects_id=${subjectsId}",
            "</when>",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "AND tt.`lesson_type` = ${lessonType} ",
            "</when>",
            "<if test= 'type == 3'>",
            "and g.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and tt.school_id in(${schoolId})",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "g.teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "tt.school_id,g.teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
            "tt.school_id",
            "</if>",
            "</script>"})
    public abstract List<ArrivalRate> getSuspensionRate(@Param("classTypeId") String classTypeId,
                                                        @Param("yearClassId") String yearClassId,
                                                        @Param("yearPart") String yearPart,
                                                        @Param("quarterNum") String quarterNum,
                                                        @Param("schoolId") String schoolId,
                                                        @Param("subjectsId") String subjectsId,
                                                        @Param("startDate") String startDate,
                                                        @Param("endDate") String endDate,
                                                        @Param("type") String type,
                                                        @Param("lessonType") String lessonType,
                                                        @Param("isHistory") Integer isHistory);

    @Select({"<script>",
            "SELECT",
            "tt.*,",
            "g.`name` AS grade_name,g.week_dates,g.time_start,g.time_end ",
            "FROM",
            "(",
            "SELECT",
            "t.*,",
            "getSchoolName ( t.school_id ) AS school_name,",
            "t1.`name` AS student_name,",
            "getSubjectsName ( t.subjects_id ) AS subjects_name,",
            "t2.grade_id, ",
            "t2.year_class_id ",
            "FROM",
            "(",
            "SELECT",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count,",
            "SUM( use_course_count ) AS use_course_count,",
            "SUM( turn_out_house ) AS turn_out_house ",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history",
            "</when>",
            "WHERE",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t",
            "INNER JOIN (",
            "SELECT",
            "* ",
            "FROM",
            "student_grade ",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND `quarter_num` = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND lesson_type = ${lessonType} ",
            "AND is_last = 1 ",
            "and status =1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON t.school_id = t2.school_id ",
            "AND t.subjects_id = t2.subjects_id ",
            "AND t.student_id = t2.student_id ",
            "AND t.lesson_type = t2.lesson_type",
            "INNER JOIN student AS t1 ON t.student_id = t1.id ",
            "WHERE",
            "t.residue_course_count > 0 ",
            "AND t.use_course_count &gt; 0",
            "and t.turn_out_house = 0",
            "and t2.reading_status = 4",
            ") AS tt",
            "LEFT JOIN grade AS g ",
            "on",
            "tt.grade_id = g.id",
            "where 1=1",
            "<when test='lessonType!=null and lessonType!=&apos;&apos; '>",
            "AND tt.`lesson_type` = ${lessonType} ",
            "</when>",
            "<if test= 'type == 1'>",
            "AND g.teacher_id = ${teacherId}",
            "</if>",
            "<if test= 'type == 2'>",
            "and tt.school_id =${schoolId}",
            "AND g.teacher_id = ${teacherId}",
            "</if>",
            "<if test= 'type == 3'>",
            "and g.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "AND g.teacher_id = ${teacherId}",
            "</if>",
            "<if test= 'type == 4'>",
            "and tt.school_id =${schoolId}",
            "</if>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and tt.subjects_id =${subjectsId}",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "AND tt.`grade_id` = ${gradeId} ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND g.`teacher_id` = ${teacherId} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "and tt.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "and course_type_id in (${classTypeId})",
            "</when>",
            "</script>"})
    public abstract List<ArrivalRate> getSuspensionRateDetail(@Param("classTypeId") String classTypeId,
                                                              @Param("yearClassId") String yearClassId,
                                                              @Param("yearPart") String yearPart,
                                                              @Param("quarterNum") String quarterNum,
                                                              @Param("schoolId") String schoolId,
                                                              @Param("teacherId") String teacherId,
                                                              @Param("gradeId") String gradeId,
                                                              @Param("subjectsId") String subjectsId,
                                                              @Param("startDate") String startDate,
                                                              @Param("endDate") String endDate,
                                                              @Param("type") String type,
                                                              @Param("lessonType") String lessonType,
                                                              @Param("isHistory") Integer isHistory);

    /**
     * 有报名的校区
     *
     * @param yearPart
     * @param quarterNum
     * @return
     */
    @Select({"<script>",
            "SELECT o.school_id,getSchoolName ( o.school_id ) as schoolName",
            "FROM order_detail AS o ",
            "WHERE o.lesson_type = 1",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and year_class_id in (${yearClassId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "and subjects_id in (${subjectsId})",
            "</when>",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "GROUP BY school_id ",
            "</script>"})
    public abstract List<StudentGrade> orderDetailList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("yearClassId") String yearClassId);


    @Select({"<script>",
            "SELECT ",
            "getSchoolName ( school_id ) AS schoolName,",
            "school_id,",
            "SUM( already_count ) AS already_count,",
            "SUM( not_already_count ) AS not_already_count ",
            "FROM ",
            "( ",
            "SELECT ",
            "alreadyCount.school_id,",
            "IFNULL( alreadyCount.already_count, 0 ) AS already_count,",
            "0 AS not_already_count ",
            "FROM ",
            "( ",
            "SELECT ",
            "COUNT(*) AS already_count,",
            "school_id ",
            "FROM ",
            "(",
            "SELECT ",
            "a.id,",
            "g.school_id,",
            "t2.residue_course_count ",
            "FROM ",
            "student AS a ",
            "LEFT JOIN student_grade AS g ON g.student_id = a.id ",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course_count,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON g.school_id = t2.school_id ",
            "AND g.student_id = t2.student_id ",
            "AND g.subjects_id = t2.subjects_id ",
            "WHERE ",
            "a.deleted = 0 ",
            "AND a.id IN ( SELECT student_id FROM order_detail WHERE deleted = 0  ",
            "<when test='isHistory==0 '>",
            "AND residue_course_count &gt; 0 ",
            "</when>",
            "AND lesson_type = 1 AND order_detail.year_part = g.year_part AND order_detail.quarter_num = g.quarter_num AND g.student_id = order_detail.student_id AND g.subjects_id = order_detail.subjects_id )",
            "AND g.`status` = 1 ",
            "AND g.deleted = 0 ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and g.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='isHistory==0 '>",
            "AND g.reading_status = 1 ",
            "</when>",
            "AND g.lesson_type = 1 ",
            "AND g.year_part = ${yearPart} ",
            "AND g.quarter_num = ${quarterNum} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND g.subjects_id = ${subjectsId} ",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY a.id,g.subjects_id ",
            ") AS alreadyCount ",
            "GROUP BY school_id ",
            ") AS alreadyCount ",
            "UNION ALL ",
            "SELECT ",
            "notAlreadyCount.school_id,",
            "0 AS already_count, ",
            "IFNULL( COUNT(*) , 0 ) AS not_already_count ",
            "FROM ",
            "( ",
            "SELECT ",
            "o.school_id ",
            "FROM ",
            "student AS a ",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,school_id,student_id,subjects_id,lesson_type,",
            "SUM( residue_course_count ) / 3 AS residue_course_count ",
            "FROM ",
            "order_detail ",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON o.school_id = t2.school_id ",
            "AND o.student_id = t2.student_id ",
            "AND o.subjects_id = t2.subjects_id ",
            "WHERE ",
            "a.deleted = 0 ",
            "AND a.id IN ( SELECT student_id FROM order_apply WHERE deleted = 0 AND STATUS &lt;&gt; 2 GROUP BY student_id )  ",
            "AND o.year_part = ${yearPart} ",
            "AND o.quarter_num = ${quarterNum} ",
            "AND o.lesson_type = 1 ",
            "AND o.`status` &lt;&gt; 2",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND o.subjects_id = ${subjectsId} ",
            "</when>",
            "<when test='isHistory==0 '>",
            "AND o.residue_course_count &gt; 0 ",
            "</when>",
            "AND o.student_id NOT IN ( SELECT student_id FROM student_grade WHERE subjects_id = o.subjects_id  AND school_id = o.school_id AND year_part = ${yearPart} AND quarter_num = ${quarterNum} AND subjects_id = o.subjects_id AND lesson_type = 1",
            "<when test='isHistory==0 '>",
            "AND reading_status = 1 ",
            "</when>",
            "AND `status` = 1  )",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and o.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY o.student_id,o.subjects_id,o.school_id ",
            ") AS notAlreadyCount ",
            "GROUP BY notAlreadyCount.school_id ",
            ") AS combinedCounts ",
            "GROUP BY school_id",
            "ORDER BY not_already_count DESC ",
            "</script>"})
    public abstract List<NotScheduled> alreadyCountNew(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("isHistory") Integer isHistory,  @Param("yearClassId") String yearClassId, @Param("startResidue") String startResidue, @Param("endResidue") String endResidue);



    /**
     * 已排班人数
     */
    @Select({"<script>",
            "SELECT ",
            "IFNULL(COUNT(*),0)AS already_count ",
            "FROM( ",
            "SELECT ",
            "a.id,g.school_id,t2.residue_course_count ",
            "FROM ",
            "student AS a ",
            "LEFT JOIN student_grade AS g ON g.student_id = a.id ",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course_count,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON g.school_id = t2.school_id ",
            "AND g.student_id = t2.student_id ",
            "AND g.subjects_id = t2.subjects_id ",
            "WHERE ",
            "a.deleted = 0 ",
            "AND a.id IN ( SELECT student_id FROM order_detail WHERE deleted = 0  ",
            "<when test='isHistory==0 '>",
            "AND residue_course_count &gt; 0 ",
            "</when>",
            "AND lesson_type = 1 AND order_detail.year_part = g.year_part AND order_detail.quarter_num = g.quarter_num AND g.student_id = order_detail.student_id AND g.subjects_id = order_detail.subjects_id )",
            "AND g.`status` = 1 ",
            "AND g.deleted = 0 ",
            "AND g.school_id IN ( ${schoolId} ) ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and g.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='isHistory==0 '>",
            "AND g.reading_status = 1 ",
            "</when>",
            "AND g.lesson_type = 1 ",
            "AND g.year_part = ${yearPart} ",
            "AND g.quarter_num = ${quarterNum} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND g.subjects_id = ${subjectsId} ",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY a.id,g.subjects_id ",
            ") AS alreadyCount ",
            "</script>"})
    public abstract Integer alreadyCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("isHistory") Integer isHistory, @Param("schoolId") Long schoolId, @Param("yearClassId") String yearClassId,@Param("startResidue") String startResidue,@Param("endResidue") String endResidue);

    /**
     * 精品课未排班人数
     */
    @Select({"<script>",
            "SELECT ",
            "IFNULL(COUNT(*),0)AS already_count ",
            "FROM( ",
            "SELECT ",
            "a.id,t2.residue_course_count ",
            "FROM ",
            "student AS a ",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN (",
            "SELECT",
            "id,",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count )/3 AS residue_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON o.school_id = t2.school_id ",
            "AND o.student_id = t2.student_id ",
            "AND o.subjects_id = t2.subjects_id ",
            "WHERE ",
            "a.deleted = 0 ",
            "AND a.id IN ( SELECT student_id FROM order_apply WHERE school_id IN ( ${schoolId} ) AND deleted = 0 AND STATUS &lt;&gt; 2 GROUP BY student_id )  ",
            "AND o.school_id IN ( ${schoolId} ) ",
            "AND o.year_part = ${yearPart} ",
            "AND o.quarter_num = ${quarterNum} ",
            "AND o.lesson_type = 1 ",
            "AND o.`status` &lt;&gt; 2",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND o.subjects_id = ${subjectsId} ",
            "</when>",
            "<when test='isHistory==0 '>",
            "AND o.residue_course_count &gt; 0 ",
            "</when>",
            "AND o.student_id NOT IN ( SELECT student_id FROM student_grade WHERE school_id IN ( ${schoolId} ) AND year_part = ${yearPart} AND quarter_num = ${quarterNum} AND subjects_id = o.subjects_id AND lesson_type = 1",
            "<when test='isHistory==0 '>",
            "AND reading_status = 1 ",
            "</when>",
            "AND `status` = 1  )",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and o.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY o.student_id,o.subjects_id ",
            ") AS notAlreadyCount ",
            "</script>"})
    public abstract Integer notAlreadyCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("schoolId") Long schoolId, @Param("yearClassId") String yearClassId, @Param("isHistory") Integer isHistory,@Param("startResidue") String startResidue,@Param("endResidue") String endResidue);

    /**
     * 智能课和刷题班排班人数
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectId
     * @param schoolId
     * @param lessonType
     * @param type
     * @return
     */
    @Select({"<script>",
            "SELECT ",
            "teacher_id,",
            "staff_name,",
            "school_id,",
            "getSchoolName( school_id ) AS school_name, ",
            "SUM(alreadyCount) AS alreadyCount, ",
            "SUM(notAlreadyCount) AS notAlreadyCount, ",
            " SUM(alreadyCount + notAlreadyCount) AS totalCount ",
            "FROM (",
            "SELECT ",
            "teacher_id, ",
            "staff_name,",
            "school_id,",
            "year_class_id,",
            "COUNT(*) AS alreadyCount,",
            "0 AS notAlreadyCount ",
            "FROM (",
            "SELECT ",
            "grade.school_id, ",
            "grade.teacher_id, ",
            "getStaffNameForId(grade.teacher_id) AS staff_name, ",
            "grade.year_class_id, ",
            "t3.student_id ",
            "FROM ",
            "student_grade AS t3 ",
            "LEFT JOIN student_grade AS t2 ON t3.student_id = t2.student_id ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.quarter_num = ${quarterNum} ",
            "AND t2.subjects_id IN (${subjectId}) ",
            "AND t2.reading_status IN ( 1, 5 ) ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.deleted = 0 ",
            "LEFT JOIN grade ON t3.grade_id = grade.id ",
            "WHERE ",
            "t3.year_part = ${yearPart} ",
            "AND t3.quarter_num = ${quarterNum} ",
            "AND t3.lesson_type = 1 ",
            "AND t3.subjects_id IN (${subjectId}) ",
            "AND t3.`status`  &lt;&gt; 3 ",
            "AND t3.reading_status IN (1, 5) ",
            "AND t3.is_last = 1 ",
            "AND t3.deleted = 0 ",
            "AND t2.student_id IS NOT NULL ",
            "AND t3.student_id not in(1600655676156149760,1700512945621958656) ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and grade.year_class_id in (${yearClassId})",
            "</when>",
            "GROUP BY ",
            "t3.student_id ,t3.subjects_id",
            ") AS alreadyScheduled ",
            "GROUP BY  ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2 or type == 4 '>",
            "school_id,teacher_id",
            "</if>",
            "UNION ALL ",
            "SELECT ",
            "teacher_id, ",
            "staff_name, ",
            "school_id, ",
            "year_class_id,",
            "0 AS alreadyCount, ",
            "COUNT(*) AS notAlreadyCount ",
            "FROM ( ",
            "SELECT ",
            "grade.school_id, ",
            "grade.teacher_id, ",
            "getStaffNameForId(grade.teacher_id) AS staff_name, ",
            "grade.year_class_id, ",
            "t3.student_id ",
            "FROM ",
            "student_grade AS t3 ",
            "LEFT JOIN student_grade AS t2 ON t3.student_id = t2.student_id ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.quarter_num = ${quarterNum} ",
            "AND t2.subjects_id IN (${subjectId}) ",
            "AND t2.reading_status IN ( 1, 5 ) ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.deleted = 0 ",
            "LEFT JOIN grade ON t3.grade_id = grade.id ",
            "WHERE ",
            "t3.year_part = ${yearPart} ",
            "AND t3.quarter_num = ${quarterNum} ",
            "AND t3.lesson_type = 1 ",
            "AND t3.subjects_id IN (${subjectId}) ",
            "AND t3.`status` &lt;&gt; 3 ",
            "AND t3.reading_status IN (1, 5) ",
            "AND t3.is_last = 1 ",
            "AND t3.deleted = 0 ",
            "AND t2.student_id IS NULL ",
            "AND t3.student_id not in(1600655676156149760,1700512945621958656) ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t3.school_id IN ( ${schoolId} ) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and grade.year_class_id in (${yearClassId})",
            "</when>",
            "GROUP BY t3.student_id ,t3.subjects_id",
            ") AS notScheduled ",
            "GROUP BY ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2 or type == 4 '>",
            "school_id,teacher_id",
            "</if>",
            ") AS combined ",
            "WHERE 1 = 1 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id IN ( ${schoolId} ) ",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</if>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id ",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id ",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id ",
            "</if>",
            "ORDER BY notAlreadyCount DESC,alreadyCount DESC  ",
            "</script>"})
    IPage<StudentGrade> notAlreadyPracticeCount(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectId") String subjectId, @Param("schoolId") String schoolId, @Param("lessonType") Integer lessonType, @Param("type") Integer type, @Param("yearClassId") String yearClassId);

    /**
     * 刷题班未排班复刷班型人数
     *
     * @param yearPart
     * @param quarterNum
     * @param subjectId
     * @param schoolId
     * @param lessonType
     * @param type
     * @return
     */
    @Select({"<script>",
            "SELECT ",
            "teacher_id,",
            "staff_name,",
            "school_id,",
            "getSchoolName( school_id ) AS school_name, ",
            "SUM(alreadyCount) AS alreadyCount, ",
            "SUM(notAlreadyCount) AS notAlreadyCount, ",
            "SUM(alreadyCount + notAlreadyCount) AS totalCount ",
            "FROM (",
            "SELECT ",
            "teacher_id, ",
            "staff_name,",
            "school_id,",
            "year_class_id,",
            "COUNT(*) AS alreadyCount,",
            "0 AS notAlreadyCount ",
            "FROM (",
            "SELECT ",
            "grade.school_id, ",
            "grade.teacher_id, ",
            "getStaffNameForId(grade.teacher_id) AS staff_name, ",
            "grade.year_class_id, ",
            "t3.student_id ",
            "FROM ",
            "student_grade AS t3 ",
            "LEFT JOIN student_grade AS t2 ON t3.student_id = t2.student_id ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.quarter_num = ${quarterNum} ",
            "AND t2.subjects_id IN (${subjectId}) ",
            "AND t2.reading_status IN ( 1, 5 ) ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.deleted = 0 ",
            "LEFT JOIN grade ON t3.grade_id = grade.id ",
            "INNER JOIN again_brush_data_temp_tb AS t7 ON ",
            "t2.student_id = t7.student_id  ",
            "AND t2.subjects_id = t7.subjects_id ",
            "WHERE ",
            "t3.year_part = ${yearPart} ",
            "AND t3.quarter_num = ${quarterNum} ",
            "AND t3.lesson_type = 1 ",
            "AND t3.subjects_id IN (${subjectId}) ",
            "AND t3.`status`  &lt;&gt; 3 ",
            "AND t3.reading_status IN (1, 5) ",
            "AND t3.is_last = 1 ",
            "AND t3.deleted = 0 ",
            "AND t2.student_id IS NOT NULL ",
            "AND t3.student_id not in(1600655676156149760,1700512945621958656) ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and grade.year_class_id in (${yearClassId})",
            "</when>",
            "GROUP BY ",
            "t3.student_id ,t3.subjects_id",
            ") AS alreadyScheduled ",
            "GROUP BY  ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2 or type == 4 '>",
            "school_id,teacher_id",
            "</if>",
            "UNION ALL ",
            "SELECT ",
            "teacher_id, ",
            "staff_name, ",
            "school_id, ",
            "year_class_id,",
            "0 AS alreadyCount, ",
            "COUNT(*) AS notAlreadyCount ",
            "FROM ( ",
            "SELECT ",
            "grade.school_id, ",
            "grade.teacher_id, ",
            "getStaffNameForId(grade.teacher_id) AS staff_name, ",
            "grade.year_class_id, ",
            "t3.student_id ",
            "FROM ",
            "student_grade AS t3 ",
            "LEFT JOIN student_grade AS t2 ON t3.student_id = t2.student_id ",
            "AND t2.lesson_type = ${lessonType} ",
            "AND t2.year_part = ${yearPart} ",
            "AND t2.quarter_num = ${quarterNum} ",
            "AND t2.subjects_id IN (${subjectId}) ",
            "AND t2.reading_status IN ( 1, 5 ) ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.deleted = 0 ",
            "LEFT JOIN grade ON t3.grade_id = grade.id ",
            "INNER JOIN again_brush_data_temp_tb2 AS t7 ON",
            "t2.student_id = t7.student_id ",
            "AND t2.subjects_id = t7.subjects_id ",
            "WHERE ",
            "t3.year_part = ${yearPart} ",
            "AND t3.quarter_num = ${quarterNum} ",
            "AND t3.lesson_type = 1 ",
            "AND t3.subjects_id IN (${subjectId}) ",
            "AND t3.`status` &lt;&gt; 3 ",
            "AND t3.reading_status IN (1, 5) ",
            "AND t3.is_last = 1 ",
            "AND t3.deleted = 0 ",
            "AND t2.student_id IS NULL ",
            "AND t3.student_id not in(1600655676156149760,1700512945621958656) ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t3.school_id IN ( ${schoolId} ) ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and grade.year_class_id in (${yearClassId})",
            "</when>",
            "GROUP BY t3.student_id ,t3.subjects_id",
            ") AS notScheduled ",
            "GROUP BY ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2 or type == 4 '>",
            "school_id,teacher_id",
            "</if>",
            ") AS combined ",
            "WHERE 1 = 1 ",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND school_id IN ( ${schoolId} ) ",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242) ",
            "</if>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id ",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id ",
            "</if>",
            "<if test= 'type == 4'>",
            "school_id ",
            "</if>",
            "ORDER BY notAlreadyCount DESC,alreadyCount DESC  ",
            "</script>"})
    List<StudentGrade> reBrushStudentCount(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectId") String subjectId, @Param("schoolId") String schoolId, @Param("lessonType") Integer lessonType, @Param("type") Integer type, @Param("yearClassId") String yearClassId);



    //调用存储过程
    @Update({"call storeAgainBrushData(#{yearPart},#{quarterNum},#{subjectId},#{yearClassId},null)"})
    public void callStoreAgainBrushData(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum,  @Param("subjectId") String subjectId, @Param("yearClassId") String yearClassId );


    /**
     * 复刷排班未排班学员详情
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @return
     */
    @Select({
            "<script>",
            "SELECT ",
            "grade.teacher_id, ",
            "getStaffNameForId ( grade.teacher_id ) AS staff_name, ",
            "getSchoolName( grade.school_id ) AS school_name, ",
            "t3.student_id, ",
            "t3.subjects_id, ",
            "t3.grade_id AS boutique_grade_id, ",
            "t6.residue_course_count,",
            "t3.year_class_id AS year_class_id ",
            "FROM ",
            "( ",
            "SELECT * ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "AND subjects_id IN (${subjectsId}) ",
            "AND `status` &lt;&gt; 3 ",
            "AND reading_status IN ( 1, 5 )  ",
            "AND is_last = 1 ",
            "AND deleted = 0 ",
            ") AS t3 ",
            "LEFT JOIN ( ",
            "SELECT * ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND subjects_id IN (${subjectsId}) ",
            "AND reading_status IN ( 1, 5 ) ",
            "AND `status` &lt;&gt; 3 ",
            "AND deleted = 0 ",
            ") AS t2 ON t3.student_id = t2.student_id ",
            "LEFT JOIN grade ON t3.grade_id = grade.id ",
            "INNER JOIN again_brush_data_temp_tb AS t7 ON ",
            "t2.student_id = t7.student_id ",
            "AND t2.subjects_id = t7.subjects_id ",
            "LEFT JOIN (",
            "SELECT",
            "id,",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t6 ON t3.school_id = t6.school_id ",
            "AND t3.student_id = t6.student_id ",
            "AND t3.subjects_id = t6.subjects_id ",
            "AND t3.lesson_type = t6.lesson_type ",
            "WHERE ",
            "t3.student_id not in(1600655676156149760,1700512945621958656) ",
            "and t2.student_id IS NULL ",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND grade.teacher_id = ${teacherId} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND grade.school_id = ${schoolId} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and grade.year_class_id in (${yearClassId})",
            "</when>",
            "</script>"})
    IPage<StudentGrade> notAlreadyBrushStudentDetail(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("teacherId") String teacherId, @Param("lessonType") Integer lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId);






    /**
     * 精品课未排班学员详情
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param schoolId
     * @return
     */
    @Select({"<script>",
            "SELECT * FROM ( ",
            "SELECT ",
//            "CONCAT( a.`name`, a.remark ) AS `studentName`,",
            " a.`name` AS `studentName`,",
            "o.subjects_id,o.school_id, ",
            "getSchoolName(o.school_id) AS schoolName, ",
            "t2.residue_course_count,",
            "o.year_class_id, ",
            "IFNULL( t3.year_and_quarter, o.year_and_quarter ) AS year_and_quarter,",
            "IFNULL( t3.handle_date, o.handle_date ) AS handle_date ",
            "FROM ",
            "student AS a ",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN (",
            "SELECT",
            "id,",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count )/3 AS residue_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON o.school_id = t2.school_id ",
            "AND o.student_id = t2.student_id ",
            "AND o.subjects_id = t2.subjects_id ",

            "LEFT JOIN ( ",
            "WITH last_lesson1 AS (",
            "SELECT",
            "t1.student_id,",
            "t1.subjects_id,",
            "MAX( t1.year_and_quarter ) AS last_year_and_quarter ",
            "FROM ",
            "order_detail AS t1 ",
            "LEFT JOIN record_course AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.`quarter` ",
            "AND t1.lesson_type = t2.lesson_type ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.deleted = t2.deleted ",
            "WHERE ",
            "t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.school_id IN ( ${schoolId} ) ",
            "AND t2.id IS NOT NULL ",
            "GROUP BY ",
            "t1.student_id,t1.subjects_id ",
            "),",
            "last_lesson2 AS (",
            "SELECT ",
            "t1.student_id,",
            "t1.subjects_id,",
            "MIN( t1.year_and_quarter ) AS last_year_and_quarter ",
            "FROM ",
            "order_detail AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.lesson_type = t2.lesson_type ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t1.deleted = t2.deleted ",
            "WHERE ",
            "t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.use_course_count = 0 ",
            "AND t1.school_id IN ( ${schoolId} ) ",
            "AND t2.id IS NULL ",
            "GROUP BY ",
            "t1.student_id,t1.subjects_id ",
            ") ",
            "SELECT ",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.year_and_quarter,",
            "t1.handle_date ",
            "FROM ",
            "order_detail AS t1 ",
            "WHERE ",
            "t1.deleted = 0 ",
            "AND t1.`status` &lt;&gt; 2 ",
            "AND t1.lesson_type = 1 ",
            "AND t1.use_course_count = 0 ",
            "AND t1.all_course_count &gt; 0 ",
            "AND t1.school_id IN ( ${schoolId} ) ",
            "AND ( ",
            "( SELECT last_year_and_quarter FROM last_lesson1 WHERE student_id = t1.student_id AND subjects_id = t1.subjects_id ) IS NOT NULL ",
            "AND t1.year_and_quarter > ( SELECT last_year_and_quarter FROM last_lesson1 WHERE student_id = t1.student_id AND subjects_id = t1.subjects_id ) ",
            "OR ( SELECT last_year_and_quarter FROM last_lesson1 WHERE student_id = t1.student_id AND subjects_id = t1.subjects_id ) IS NULL ",
            "AND t1.year_and_quarter >= ( SELECT last_year_and_quarter FROM last_lesson2 WHERE student_id = t1.student_id AND subjects_id = t1.subjects_id ) ",
            ") ",
            "GROUP BY ",
            "t1.school_id, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t1.lesson_type ",
            ") AS t3 ON o.student_id = t3.student_id ",
            "AND o.subjects_id = t3.subjects_id ",

            "WHERE ",
            "a.deleted = 0 ",
//            "AND a.id IN ( SELECT student_id FROM order_apply WHERE school_id IN ( ${schoolId} ) AND deleted = 0 AND STATUS &lt;&gt; 2 GROUP BY student_id ) ",
            "AND o.school_id IN ( ${schoolId} ) ",
            "AND o.year_part = ${yearPart} ",
            "AND o.quarter_num = ${quarterNum} ",
            "<when test='isHistory==0 '>",
            "AND o.residue_course_count &gt; 0 ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND o.subjects_id = ${subjectsId}  ",
            "</when>",
            "AND o.`status` &lt;&gt; 2",
            "AND o.lesson_type = 1 ",
            "AND o.student_id NOT IN ( SELECT student_id FROM student_grade WHERE school_id IN ( ${schoolId} ) AND year_part = ${yearPart} AND quarter_num = ${quarterNum} AND subjects_id = o.subjects_id AND lesson_type = 1",
            "<when test='isHistory==0 '>",
            "AND reading_status = 1 ",
            "</when>",
            "AND `status` = 1  )  ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and o.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY o.student_id,o.subjects_id",
            ") AS tt ",
            "WHERE 1=1 ",
            "<when test='yearAndQuarter!=null and yearAndQuarter!=&apos;&apos;'>",
            "AND year_and_quarter = ${yearAndQuarter} ",
            "</when>",
            "</script>"})
    IPage<StudentGrade> notAlreadyStudent(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("isHistory") Integer isHistory, @Param("startResidue") String startResidue, @Param("endResidue") String endResidue, @Param("yearAndQuarter") String yearAndQuarter);

    /**
     * 精品课已排班学员详情
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param schoolId
     * @return
     */
    @Select({"<script>",
            "SELECT ",
            "CONCAT( a.`name`, a.remark ) AS `studentName`,",
            "g.subjects_id,",
            "getSchoolName ( g.school_id ) AS schoolName,",
            "t2.residue_course_count,",
            "g.year_class_id ",
            "FROM student AS a ",
            "LEFT JOIN student_grade AS g ON g.student_id = a.id ",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count ) /3 AS residue_course_count,school_id,subjects_id,student_id ",
            "FROM order_detail",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON g.school_id = t2.school_id  ",
            "AND g.student_id = t2.student_id ",
            "AND g.subjects_id = t2.subjects_id ",
            "WHERE ",
            "a.deleted = 0 ",
            "AND g.`status` = 1 ",
            "AND g.deleted = 0 ",
            "AND g.reading_status = 1 ",
            "AND g.lesson_type = 1 ",
            "AND a.id IN ( SELECT student_id FROM order_detail WHERE deleted = 0 AND lesson_type = 1 AND order_detail.year_part = g.year_part ",
            "<when test='isHistory==0 '>",
            "AND t2.residue_course_count &gt; 0 ",
            "</when>",
            "AND order_detail.quarter_num = g.quarter_num) ",
            "AND g.school_id IN ( ${schoolId} ) ",
            "AND g.year_part = ${yearPart} ",
            "AND g.quarter_num = ${quarterNum} ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND g.subjects_id = ${subjectsId}  ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and g.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='isHistory==0 '>",
            "AND g.reading_status = 1 ",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY a.id,g.subjects_id ",
            "</script>"})
    IPage<StudentGrade> alreadyStudent(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("isHistory") String isHistory,@Param("startResidue") String startResidue,@Param("endResidue") String endResidue);

    /**
     * 精品课总排班学员详情
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param schoolId
     * @return
     */
    @Select({"<script>",
            "SELECT ",
            "CONCAT( a.`name`, a.remark ) AS `studentName`,",
            "o.subjects_id,",
            "o.school_id,",
            "getSchoolName ( o.school_id ) AS schoolName,",
            "t2.residue_course_count,",
            "o.year_class_id ",
            "FROM ",
            "student AS a",
            "INNER JOIN order_detail AS o ON a.id = o.student_id ",
            "INNER JOIN ( ",
            "SELECT ",
            "id,SUM( residue_course_count )/3 AS residue_course_count,school_id,subjects_id,student_id",
            "FROM ",
            "order_detail ",
            "WHERE ",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND lesson_type = 1",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t2 ON o.school_id = t2.school_id ",
            "AND o.student_id = t2.student_id ",
            "AND o.subjects_id = t2.subjects_id ",
            "LEFT JOIN student_grade AS g ON g.student_id = a.id AND g.year_part = ${yearPart} AND g.quarter_num = ${quarterNum} AND g.subjects_id = o.subjects_id AND g.reading_status = 1 AND g.`status` = 1 AND g.lesson_type = 1 AND g.deleted = 0 AND g.school_id IN ( ${schoolId} )",
            "WHERE ",
            "a.deleted = 0 ",
            "AND a.id IN ( SELECT student_id FROM order_apply WHERE school_id IN ( ${schoolId} ) AND deleted = 0 AND `status` &lt;&gt; 2 GROUP BY student_id ) ",
            "AND o.year_part = ${yearPart} ",
            "AND o.quarter_num = ${quarterNum} ",
            "AND o.school_id IN ( ${schoolId}) ",
            "<when test='isHistory==0 '>",
            "AND o.residue_course_count &gt; 0 ",
            "</when>",
            "AND o.`status` &lt;&gt; 2 ",
            "AND o.lesson_type = 1 ",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos; '>",
            "AND o.subjects_id = ${subjectsId}  ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and o.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos; and endResidue!=null and endResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count BETWEEN ${startResidue} AND ${endResidue}",
            "</when>",
            "<when test='startResidue!=null and startResidue!=&apos;&apos;'>",
            "AND t2.residue_course_count &gt;= ${startResidue} ",
            "</when>",
            "GROUP BY o.student_id,o.subjects_id,o.school_id  ",
            "ORDER BY a.id",
            "</script>"})
    IPage<StudentGrade> alreadyAllStudent(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId, @Param("isHistory") String isHistory,@Param("startResidue") String startResidue,@Param("endResidue") String endResidue);


    /**
     * 精品课已排班，智能课刷题班未排班学员详情
     *
     * @param page
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @return
     */
    @Select({
            "<script>",
            "SELECT ",
            "grade.teacher_id, ",
            "getStaffNameForId ( grade.teacher_id ) AS staff_name, ",
            "getSchoolName( grade.school_id ) AS school_name, ",
            "t3.student_id, ",
            "t3.subjects_id, ",
            "t3.grade_id AS boutique_grade_id, ",
            "t6.residue_course_count,",
            "t3.year_class_id AS year_class_id ",
            "FROM ",
            "( ",
            "SELECT * ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "AND subjects_id IN (${subjectsId}) ",
            "AND `status` &lt;&gt; 3 ",
            "AND reading_status IN ( 1, 5 )  ",
            "AND is_last = 1 ",
            "AND deleted = 0 ",
            ") AS t3 ",
            "LEFT JOIN ( ",
            "SELECT * ",
            "FROM ",
            "student_grade ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = ${lessonType} ",
            "AND subjects_id IN (${subjectsId}) ",
            "AND reading_status IN ( 1, 5 ) ",
            "AND `status` &lt;&gt; 3 ",
            "AND deleted = 0 ",
            ") AS t2 ON t3.student_id = t2.student_id ",
            "LEFT JOIN grade ON t3.grade_id = grade.id ",
            "LEFT JOIN (",
            "SELECT",
            "id,",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) AS residue_course_count ",
            "FROM",
            "order_detail ",
            "WHERE",
            "deleted = 0 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id,",
            "lesson_type ",
            ") AS t6 ON t3.school_id = t6.school_id ",
            "AND t3.student_id = t6.student_id ",
            "AND t3.subjects_id = t6.subjects_id ",
            "AND t3.lesson_type = t6.lesson_type ",
            "WHERE ",
            "t3.student_id not in(1600655676156149760,1700512945621958656) ",
            "and t2.student_id IS NULL ",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND teacher_id = ${teacherId} ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "AND grade.school_id = ${schoolId} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos; '>",
            "and grade.year_class_id in (${yearClassId})",
            "</when>",
            "</script>"})
    IPage<StudentGrade> notAlreadyPracticeStudent(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("subjectsId") String subjectsId, @Param("teacherId") String teacherId, @Param("lessonType") Integer lessonType, @Param("schoolId") String schoolId, @Param("yearClassId") String yearClassId);


    //"INNER JOIN course ON id = csd.course_id AND lesson_type = 3 AND year_class_id IN (${yearClassId}) AND quarter_num IN (${quarterNum}) ${courseTypeId} " +
    @Select("${lessonNum}")
    public List<StudentGrade> getTaskList(@Param("lessonNum") String lessonNum);

    @Select("SELECT sg.*,csd.school_date,csd.lesson_num,(SELECT student_name FROM student_account WHERE student_id = sg.student_id LIMIT 1) AS studentName " +
            "${field} FROM student_grade AS sg " +
            "INNER JOIN course_scheduling_detail AS csd ON csd.grade_id = sg.grade_id AND csd.lesson_type = 3 AND csd.course_id = sg.course_id AND csd.school_id = sg.school_id " +
            "AND csd.grade_id IN (SELECT id FROM grade WHERE id = csd.grade_id AND year_class_id IN (${yearClassId}) AND quarter_num IN (${quarterNum}) ${courseTypeId} ) " +
            "AND csd.lesson_num IN (${lessonNum}) " +
            "AND csd.deleted = 0 " +
            "${ew.customSqlSegment}")
    public IPage<StudentGrade> getTaskListYan(IPage<?> page, @Param("lessonNum") String lessonNum, @Param("field") String field, @Param("yearClassId") String yearClassId, @Param("quarterNum") String quarterNum, @Param("courseTypeId") String courseTypeId, @Param(Constants.WRAPPER) Wrapper wrapper);

    //退费列表
    @Select({"<script>",
            "SELECT",
            "t.*,",
            "<if test= 'type == 6 '>",
                "t01.school_id,",
                "t01.pid AS schoolPid,",
                "t01.school_name,",
            "</if>",
            "<if test= 'type != 6 '>",
                "getSchoolName(t.school_id) AS SchoolName,",
            "</if>",
            "getStaffNameForId(t.teacher_id) AS teacherName,",
            "count(*) as studentAllcount,",
            "SUM( return_count_id ) AS return_count  ",
            "FROM",
            "<if test= 'type == 6 '>",
                "( SELECT * FROM school_quarter WHERE year_part = 2025 AND quarter_num = 2 AND deleted = 0 ) AS t01",
                "LEFT JOIN",
            "</if>",
            "(",
            "SELECT",
            "t1.*,",
            "t3.school_id,",
            "t3.`name` AS grade_name,",
            "t3.teacher_id,",
            "t2.reading_status,",
            "t2.`status`,",
            "t3.year_class_id,",
            "t3.course_type_id as class_type_id, ",
            "COUNT( t4.id ) AS return_count_id ",
            "FROM",
            "(",
            "SELECT",
            "year_part,",
            "quarter_num,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) residue_course_count ",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history",
            "</when>",
            "WHERE",
            "deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND lesson_type = 1 ",
            "AND use_course_count > 0 ",
            "GROUP BY",
            "student_id,",
            "subjects_id ",
            ") AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.subjects_id = t2.subjects_id  ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.lesson_type = t2.lesson_type  ",
            "AND t2.is_last = 1 ",
            "AND t2.deleted = 0  ",
            "AND t2.`status` &lt;&gt; 3 ",
            "INNER JOIN grade AS t3 ON t2.grade_id = t3.id  ",
            "LEFT JOIN ( ",
            "SELECT * FROM order_apply ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND lesson_type = 1 ",
            "AND quarter_num = ${quarterNum} ",
            "AND order_type = 4 AND (apply_type &lt;&gt; 5 OR apply_type IS NULL ) ",
            "AND `status` &lt;&gt; 2 ",
            "GROUP BY",
            "student_id,subjects_id_str ",
            ") t4 ON t4.student_id = t2.student_id ",
            "AND t4.year_part = t1.year_part ",
            "AND t4.lesson_type = t1.lesson_type ",
            "AND t4.quarter_num = t1.quarter_num ",
            "AND t4.subjects_id_str = t1.subjects_id ",
            "AND t4.order_type = 4 AND ( t4.apply_type &lt;&gt; 5 OR t4.apply_type IS NULL ) ",
            "AND t4.`status` &lt;&gt; 2 ",
            "GROUP BY ",
            "t2.student_id,t2.subjects_id  ",
            ") AS t ",
            "<if test= 'type == 6 '>",
                "ON t01.school_id = t.school_id",
            "</if>",
            "where 1=1",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "and t.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                "and t.class_type_id in (${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "and t.subjects_id=${subjectsId}",
            "</when>",
            "<if test= 'type == 3'>",
                "and t.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
                "and t.school_id in(${schoolId})",
            "</when>",
            "GROUP BY",
            "<if test= 'type == 1 or type == 3'>",
                "t.teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
                "t.school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4 or type == 6'>",
                "t.school_id",
            "</if>",
            "</script>"})
    public abstract List<ArrivalRate> getReturnPremiumRate(@Param("classTypeId") String classTypeId,
                                                           @Param("yearClassId") String yearClassId,
                                                           @Param("yearPart") String yearPart,
                                                           @Param("quarterNum") String quarterNum,
                                                           @Param("schoolId") String schoolId,
                                                           @Param("subjectsId") String subjectsId,
                                                           @Param("startDate") String startDate,
                                                           @Param("endDate") String endDate,
                                                           @Param("type") String type,
                                                           @Param("lessonType") String lessonType,
                                                           @Param("isHistory") Integer isHistory);

    @Select({"<script>",
            "SELECT",
            "t1.*,",
            "getStudentNameForId ( t1.student_id ) AS student_name,",
            "getSubjectsName(t1.subjects_id) AS subjectsName, ",
            "t3.school_id,",
            "t3.`name` AS grade_name,",
            "t3.teacher_id,",
            "t3.year_class_id,",
            "t3.course_type_id as class_type_id, ",
            "COUNT( t4.id ) AS return_count ",
            "FROM",
            "(",
            "SELECT",
            "year_part,",
            "quarter_num,",
            "student_id,",
            "subjects_id,",
            "lesson_type,",
            "SUM( residue_course_count ) residue_course_count ",
            "FROM",
            "<when test='isHistory == 0'>",
            "order_detail",
            "</when>",
            "<when test='isHistory == 1'>",
            "order_detail_history",
            "</when>",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND deleted = 0 ",
            "AND `status` &lt;&gt; 2 ",
            "AND use_course_count > 0 ",
            "AND year_part = ${yearPart} ",
            "AND lesson_type = 1 ",
            "GROUP BY",
            "school_id,",
            "student_id,",
            "subjects_id ",
            ") AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.subjects_id = t2.subjects_id  ",
            "AND t1.student_id = t2.student_id ",
            "AND t1.lesson_type = t2.lesson_type  ",
            "AND t2.is_last = 1 ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3",
            "INNER JOIN grade AS t3 ON t2.grade_id = t3.id  ",
            "LEFT JOIN ( ",
            "SELECT * FROM order_apply ",
            "WHERE ",
            "year_part = ${yearPart} ",
            "AND lesson_type = 1 ",
            "AND quarter_num = ${quarterNum} ",
            "AND order_type = 4 AND (apply_type &lt;&gt; 5 OR apply_type IS NULL ) ",
            "AND `status` &lt;&gt; 2 ",
            "GROUP BY",
            "student_id,subjects_id_str ",
            ") t4 ON t4.student_id = t2.student_id ",
            "AND t4.year_part = t1.year_part ",
            "AND t4.lesson_type = t1.lesson_type ",
            "AND t4.quarter_num = t1.quarter_num ",
            "AND t4.subjects_id_str = t1.subjects_id ",
            "AND t4.order_type = 4 AND ( t4.apply_type &lt;&gt; 5 OR t4.apply_type IS NULL ) ",
            "AND t4.`status` &lt;&gt; 2 ",
            "where 1=1",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "and t2.year_class_id in (${yearClassId})",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "and course_type_id in (${classTypeId})",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "and t2.subjects_id = ${subjectsId}",
            "</when>",
            "<if test= 'type == 3'>",
            "and teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos; '>",
            "and t2.school_id in(${schoolId})",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos; '>",
            "AND `teacher_id` = ${teacherId} ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos; '>",
            "AND grade_id = ${gradeId} ",
            "</when>",
            "GROUP BY ",
            "t2.student_id,t2.subjects_id ",
            "HAVING return_count &gt;=1 ",
            "</script>"})
    public abstract List<ArrivalRate> getReturnPremiumRateDetail(@Param("classTypeId") String classTypeId,
                                                                 @Param("yearClassId") String yearClassId,
                                                                 @Param("yearPart") String yearPart,
                                                                 @Param("quarterNum") String quarterNum,
                                                                 @Param("schoolId") String schoolId,
                                                                 @Param("teacherId") String teacherId,
                                                                 @Param("gradeId") String gradeId,
                                                                 @Param("subjectsId") String subjectsId,
                                                                 @Param("startDate") String startDate,
                                                                 @Param("endDate") String endDate,
                                                                 @Param("type") String type,
                                                                 @Param("lessonType") String lessonType,
                                                                 @Param("isHistory") Integer isHistory);

    /**
     * 获取本学期使用课时(不分校区)
     *
     * @param
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "IFNULL( sum( t1.use_course_count ), 0 ) AS count ",
            "FROM",
            "student_grade AS t1",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "AND t1.lesson_type = ${lessonType} ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS &lt;&gt; 3 ",
            "AND t2.is_makeup = 0",
            "</script>"})
    public abstract Long getStudentUseCourseCountAllSchool(@Param("yearPart") Integer yearPart,
                                                           @Param("quarterNum") Integer quarterNum,
                                                           @Param("subjectsId") Long subjectsId,
                                                           @Param("studentId") Long studentId,
                                                           @Param("lessonType") Integer lessonType);

    /**
     * 浮动排班列表
     */
    @Select({"<script>",
            "SELECT",
            "<if test= 'type != 6'>",
            "t02.id,",
            "t02.teacher_id,",
            "t02.school_id,",
            "t02.school_name,",
            "SUM( full_count ) AS full_count,",
            "</if>",

            "<if test= 'type == 6'>",
            "t01.pid AS school_pid,",
            "t01.school_id,",
            "getSchoolName ( t01.school_id ) AS school_name,",
            "IFNULL(SUM( CASE WHEN full_count IS NOT NULL ",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND year_class_id in (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "AND course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND subjects_id in (${subjectsId}) ",
            "</when>",
            "THEN t02.full_count ELSE 0 END  ),0) AS full_count,",
            "</if>",

            "t02.teacher_name,",
            "t02.class_type_name,",
            "t02.class_room_name,",

            "IFNULL(SUM( student_count ),0) AS student_count",
            "FROM",
            "<if test= 'type == 6'>",
            "( SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0) AS t01 ",
            "LEFT JOIN (",
            "</if>",
            "<if test= 'type != 6'>",
            "(",
            "</if>",
            "SELECT ",
            "t1.id,",
            "t1.teacher_id,",
            "t1.school_id,",
            "t1.subjects_id,",
            "t1.year_class_id,",
            "t1.course_type_id,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "getCourseTypeName(t1.course_type_id) AS class_type_name,",
            "getClassNameForId ( t1.class_room_id ) AS class_room_name,",
            "full_count,",
            "SUM( CASE WHEN t2.id IS NOT NULL ",
            "<if test= 'type == 6'>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "AND t1.year_class_id in (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                "AND t1.course_type_id in (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "AND t1.subjects_id in (${subjectsId}) ",
                "</when>",
            "</if>",
            "THEN 1 ELSE 0 END ) AS student_count ",
            "FROM grade AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.id = t2.grade_id ",
            "AND (t2.reading_status IN ( 0, 1 ) OR ( t2.use_course_count &gt; 0 )) ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.is_last = 1",
            "<when test='date!=null and date!=&apos;&apos;'>",
            "AND t2.created_at &lt;= '${date}' ",
            "</when>",
            "WHERE t1.lesson_type = 1 and t1.full_count &gt;0 AND t1.deleted = 0 ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND t1.year_part = ${yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND t1.quarter_num = ${quarterNum} ",
            "</when>",
            "<if test= 'type != 6'>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "AND t1.year_class_id in (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                "AND t1.course_type_id in (${classTypeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "AND t1.subjects_id in (${subjectsId}) ",
                "</when>",
            "</if>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<if test= 'type == 3'>",
            "AND t1.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
            "</if>",
            "GROUP BY",
            "t1.id ",
            "<if test= 'type == 6'>",
            ") AS t02 ON t01.school_id = t02.school_id ",
            "</if>",
            "<if test= 'type != 6'>",
            ") AS t02 ",
            "</if>",
            "GROUP BY ",
            "<if test= 'type == 1 or type == 3'>",
            "teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
            "school_id,teacher_id",
            "</if>",
            "<if test= 'type == 4 or type == 6'>",
            "school_id",
            "</if>",
            "</script>"})
    public abstract List<Grade> getGradeFloatList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("type") String type, @Param("date") String date);

    /**
     * 浮动排班详情
     */
    @Select({"<script>",
            "SELECT ",
            "t1.*,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "getCourseTypeName(t1.course_type_id) AS class_type_name,",
            "getClassNameForId ( t1.class_room_id ) AS class_room_name,",
            "getStudentNameForId(t2.student_id) AS student_name ",
            "FROM grade AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.id = t2.grade_id ",
            "AND (t2.reading_status IN ( 0, 1 ) OR ( t2.use_course_count &gt; 0 )) ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3 ",
            "WHERE t1.lesson_type = 1 AND t2.is_last = 1 and t1.full_count &gt;0 AND t1.deleted = 0 ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND t1.year_part = ${yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND t1.quarter_num = ${quarterNum} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t1.year_class_id in (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "AND t1.course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "AND t1.id = ${gradeId} ",
            "</when>",
            "<when test='date!=null and date!=&apos;&apos;'>",
            "AND t2.created_at &lt;= '${date}' ",
            "</when>",
            "GROUP BY t2.id",
            "</script>"})
    public abstract IPage<Grade> getGradeFloatDetails(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("gradeId") String gradeId, @Param("teacherId") String teacherId, @Param("date") String date);

    @Select({"<script>",
            "SELECT t02.team_id,t02.team_pid,t02.team_name,t02.sourse_id AS teacher_id,",
            "getStaffNameForId ( t02.sourse_id ) AS teacher_name,",
            "IFNULL( SUM( t.full_count ), 0 ) AS full_count,",
            "IFNULL( SUM( t.student_count ), 0 ) AS student_count ",
            "FROM (",
            "SELECT td.*,tm.teacher_id,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_detail AS td ",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = 18 ",
            "AND tm.track_type = 2 ",
            "AND tm.STATUS = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "<when test='teamId!=null and teamId!=&apos;&apos;'>",
            "AND tm.id IN (${teamId}) ",
            "</when>",
            "GROUP BY td.track_id,td.sourse_id ",
            ") AS t02",
            "LEFT JOIN (",
            "SELECT ",
            "t1.id,",
            "t1.teacher_id,",
            "t1.year_class_id,",
            "t1.course_type_id,",
            "t1.school_id,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "getCourseTypeName(t1.course_type_id) AS class_type_name,",
            "getClassNameForId ( t1.class_room_id ) AS class_room_name,",
            "full_count,",
            "SUM( CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END ) AS student_count",
            "FROM grade AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.id = t2.grade_id ",
            "AND (t2.reading_status IN ( 0, 1 ) OR ( t2.use_course_count &gt; 0 )) ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3 ",
            "AND t2.is_last = 1",
            "<when test='date!=null and date!=&apos;&apos;'>",
            "AND t2.created_at &lt;= '${date}' ",
            "</when>",
            "WHERE t1.lesson_type = 1 and t1.full_count &gt;0 AND t1.deleted = 0 ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND t1.year_part = ${yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND t1.quarter_num = ${quarterNum} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t1.year_class_id in (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "AND t1.course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "GROUP BY",
            "t1.id ",
            ") AS t ON t.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            "GROUP BY t02.team_id ",
            "<if test= 'isDetails == 1 '>",
            ",t02.sourse_id",
            "</if>",
            "</script>"})
    public abstract List<Grade> getGradeFloatTeamList(@Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("isDetails") String isDetails, @Param("date") String date, @Param("teamId") String teamId);

    @Select({"<script>",
            "SELECT * FROM (",
            "SELECT t.* ",
            "FROM (",
            "SELECT td.*,tm.teacher_id,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
            "FROM ",
            "track_detail AS td ",
            "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id = td.track_id ",
            "AND tm.deleted = td.deleted ",
            "WHERE ",
            "tm.classify = 18 ",
            "AND tm.track_type = 2 ",
            "AND tm.STATUS = 1 ",
            "AND tm.deleted = 0 ",
            "AND tm.year_part = ${yearPart} ",
            "AND tm.quarter_num = ${quarterNum} ",
            "AND tm.id IN (${teamId}) ",
            "GROUP BY td.track_id,td.sourse_id ",
            ") AS t02",
            "LEFT JOIN (",
            "SELECT ",
            "t1.*,",
            "getStaffNameForId ( t1.teacher_id ) AS teacher_name,",
            "getSchoolName ( t1.school_id ) AS school_name,",
            "getCourseTypeName(t1.course_type_id) AS class_type_name,",
            "getClassNameForId ( t1.class_room_id ) AS class_room_name,",
            "getStudentNameForId(t2.student_id) AS student_name ",
            "FROM grade AS t1 ",
            "LEFT JOIN student_grade AS t2 ON t1.id = t2.grade_id ",
            "AND (t2.reading_status IN ( 0, 1 ) OR ( t2.use_course_count &gt; 0 )) ",
            "AND t2.deleted = 0 ",
            "AND t2.`status` &lt;&gt; 3 ",
            "WHERE t1.lesson_type = 1 AND t2.is_last = 1 and t1.full_count &gt;0 AND t1.deleted = 0 ",
            "<when test='yearPart!=null and yearPart!=&apos;&apos;'>",
            "AND t1.year_part = ${yearPart} ",
            "</when>",
            "<when test='quarterNum!=null and quarterNum!=&apos;&apos;'>",
            "AND t1.quarter_num = ${quarterNum} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
            "AND t1.year_class_id in (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
            "AND t1.course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
            "AND t1.subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
            "AND t1.school_id in (${schoolId}) ",
            "</when>",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
            "AND t1.teacher_id = ${teacherId} ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
            "AND t1.id = ${gradeId} ",
            "</when>",
            "<when test='date!=null and date!=&apos;&apos;'>",
            "AND t2.created_at &lt;= '${date}' ",
            "</when>",
            "GROUP BY t2.id",
            ") AS t ON t.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t.course_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            "WHERE t02.sourse_id  = ${teacherId} ",
            "HAVING t.id IS NOT null",
            ") as tt",
            "</script>"})
    public abstract IPage<Grade> getGradeFloatTeamDetailsList(IPage<?> page, @Param("yearPart") String yearPart, @Param("quarterNum") String quarterNum, @Param("yearClassId") String yearClassId, @Param("classTypeId") String classTypeId, @Param("subjectsId") String subjectsId, @Param("schoolId") String schoolId, @Param("date") String date, @Param("teamId") String teamId, @Param("teacherId") String teacherId, @Param("gradeId") String gradeId);

    /**
     * 获取最后一条数据，不区分is_last
     * @param yearPart
     * @param quarterNum
     * @param subjectsId
     * @param studentId
     * @param lessonType
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "t1.school_id,",
            "t1.student_id,",
            "t1.subjects_id,",
            "t1.year_class_id,",
            "t1.lesson_type,",
            "t1.year_part,",
            "t1.quarter_num,",
            "t1.grade_id",
            "FROM",
            "student_grade AS t1",
            "WHERE",
            "t1.deleted = 0 ",
            "AND t1.lesson_type = ${lessonType}",
            "AND t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "and t1.status &lt;&gt; 3",
            "ORDER BY",
            "t1.id DESC ",
            "LIMIT 1",
            "</script>"})
    public abstract StudentGrade getLastOne(@Param("yearPart") Integer yearPart,
                                            @Param("quarterNum") Integer quarterNum,
                                            @Param("subjectsId") Long subjectsId,
                                            @Param("studentId") Long studentId,
                                            @Param("lessonType") Integer lessonType);

    /**
     * 获取本学期使用课时
     * @param
     * @return
     */
    @Select({"<script>",
            "SELECT",
            "IFNULL( sum( t1.use_course_count ), 0 ) AS count ",
            "FROM",
            "student_grade AS t1",
            "LEFT JOIN grade AS t2 ON t1.grade_id = t2.id ",
            "WHERE",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND t1.subjects_id = ${subjectsId} ",
            "AND t1.student_id = ${studentId} ",
            "AND t1.lesson_type = ${lessonType} ",
            "AND t1.deleted = 0 ",
            "AND t1.STATUS &lt;&gt; 3 ",
            "AND t2.is_makeup = 0",
            "</script>"})
    public abstract Long getStudentUseCourseCount(@Param("yearPart") Integer yearPart,
                                                  @Param("quarterNum") Integer quarterNum,
                                                  @Param("subjectsId") Long subjectsId,
                                                  @Param("studentId") Long studentId,
                                                  @Param("lessonType") Integer lessonType);

    @Select("select t1.* from student_grade as t1 INNER JOIN (select MAX(id) as id from student_grade where deleted =0 and `status`<>3 and year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type = ${lessonType} and student_id =${studentId} and subjects_id = ${subjectsId}  GROUP BY student_id,subjects_id) as t2 on t1.id = t2.id limit 1")
    public StudentGrade getLastForMaxId(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("subjectsId") Long subjectsId, @Param("studentId") Long studentId, @Param("lessonType") Integer lessonType);


    @Select({"<script>",
            "SELECT",
            "*,",
            "SUM( CASE WHEN is_adjust = 1 THEN 1 ELSE 0 END ) AS yet_adjust_count, ",
            "SUM( CASE WHEN is_adjust = 0 AND course_type_id &lt;&gt; class_type_id THEN 1 ELSE 0 END ) AS not_adjust_count ",
            "<if test= 'type == 6'>",
                ",t02.pid AS school_pid",
                "FROM ( ",
                    "SELECT * FROM school_quarter WHERE year_part = ${yearPart} AND quarter_num = ${quarterNum} AND deleted = 0",
                ") AS t02 ",
                "LEFT JOIN (",
            "</if>",
            "<if test= 'type != 6'>",
                "FROM (",
            "</if>",
                "SELECT ",
                "t1.id, ",
                "t1.year_part, ",
                "t1.quarter_num, ",
                "t1.student_id, ",
                "t1.subjects_id, ",
                "t1.class_type_id, ",
                "t3.school_id, ",
                "t3.teacher_id, ",
                "t3.course_type_id, ",
                "t3.year_class_id, ",
                "t1.is_adjust, ",
                "getSchoolName(t3.school_id) AS school_name,",
                "getStaffNameForId(t3.teacher_id) AS teacher_name ",
                "FROM ",
                "`student_class_type` AS t1 ",
                "INNER JOIN student_grade AS t2 ON t1.student_id = t2.student_id ",
                "AND t1.year_part = t2.year_part ",
                "AND t1.quarter_num = t2.quarter_num ",
                "AND t1.subjects_id = t2.subjects_id ",
                "AND t2.lesson_type = 1 ",
                "AND t2.is_last = 1 ",
                "AND t2.deleted = 0 ",
                "INNER JOIN grade AS t3 ON t3.id = t2.grade_id ",
                "AND t1.year_part = t3.year_part ",
                "AND t1.quarter_num = t3.quarter_num ",
                "AND t3.deleted = 0 ",
                "WHERE ",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND is_fit = 1 ",
                "<if test= 'type == 3'>",
                    "AND t3.teacher_id in(select admin_id from role_admin where role_id = 1568160887369994242)",
                "</if>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                    "AND t3.year_class_id in (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                    "AND t3.course_type_id in (${classTypeId}) ",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                    "AND t3.school_id in (${schoolId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                    "AND t3.subjects_id in (${subjectsId}) ",
                "</when>",
                "<when test='name!=null and name!=&apos;&apos;'>",
                    "HAVING teacher_name LIKE '%${name}%' ",
                "</when>",
            "<if test= 'type == 6'>",
                ") AS t ON t.school_id = t02.school_id ",
            "</if>",
            "<if test= 'type != 6'>",
                ") AS t ",
            "</if>",
            "GROUP BY ",
            "<if test= 'type == 1 or type == 3'>",
                "t.teacher_id",
            "</if>",
            "<if test= 'type == 2'>",
                "t.school_id,t.teacher_id",
            "</if>",
            "<if test= 'type == 4'>",
                "t.school_id",
            "</if>",
            "<if test= 'type == 6'>",
                "t02.school_id",
            "</if>",
            "ORDER BY not_adjust_count DESC ",
            "</script>"})
    public abstract List<StudentGrade> adjustGradeList(@Param("yearPart") Integer yearPart,
                                                       @Param("quarterNum") Integer quarterNum,
                                                       @Param("schoolId") String schoolId,
                                                       @Param("yearClassId") String yearClassId,
                                                       @Param("type") Integer type,
                                                       @Param("subjectsId") String subjectsId,
                                                       @Param("classTypeId") String classTypeId,
                                                       @Param("name") String name);

    @Select({"<script>",
            "SELECT ",
            "t1.id, ",
            "t1.year_part, ",
            "t1.quarter_num, ",
            "t1.student_id, ",
            "t1.subjects_id, ",
            "t1.class_type_id, ",
            "t3.year_class_id, ",
            "t3.school_id, ",
            "t3.teacher_id, ",
            "t3.course_type_id, ",
            "t1.is_adjust, ",
            "getCourseTypeName(t1.class_type_id) AS class_type_str, ",
            "getCourseTypeName(t3.course_type_id) AS course_type_str, ",
            "getStudentNameForId(t1.student_id) AS student_name ",
            "FROM ",
            "student_class_type AS t1 ",
            "INNER JOIN student_grade AS t2 ON t1.student_id = t2.student_id ",
            "AND t1.year_part = t2.year_part ",
            "AND t1.quarter_num = t2.quarter_num ",
            "AND t1.subjects_id = t2.subjects_id ",
            "AND t2.lesson_type = 1 ",
            "AND t2.is_last = 1 ",
            "AND t2.deleted = 0 ",
            "INNER JOIN grade AS t3 ON t3.id = t2.grade_id ",
            "AND t1.year_part = t3.year_part ",
            "AND t1.quarter_num = t3.quarter_num ",
            "AND t3.deleted = 0 ",
            "WHERE ",
            "t1.year_part = ${yearPart} ",
            "AND t1.quarter_num = ${quarterNum} ",
            "AND is_fit = 1 ",
            "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                "AND t3.teacher_id = ${teacherId} ",
            "</when>",
            "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                "AND t3.year_class_id in (${yearClassId}) ",
            "</when>",
            "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                "AND t3.course_type_id in (${classTypeId}) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                "AND t3.school_id in (${schoolId}) ",
            "</when>",
            "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                "AND t3.subjects_id in (${subjectsId}) ",
            "</when>",
            "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                "AND t3.id in (${gradeId}) ",
            "</when>",
            "<if test= 'detailType == 0 '>",
                "AND ((t1.is_adjust = 0 AND t1.class_type_id &lt;&gt; t3.course_type_id) OR t1.is_adjust = 1) ",
            "</if>",
            "<if test= 'detailType == 1 '>",
                "AND t1.is_adjust = 1 ",
            "</if>",
            "<if test= 'detailType == 2 '>",
                "AND t1.is_adjust = 0 AND t1.class_type_id &lt;&gt; t3.course_type_id ",
            "</if>",
            "</script>"})
    public abstract IPage<StudentGrade> adjustGradeDetails(IPage<?> page,
                                                           @Param("yearPart") Integer yearPart,
                                                           @Param("quarterNum") Integer quarterNum,
                                                           @Param("detailType") Integer detailType,
                                                           @Param("schoolId") String schoolId,
                                                           @Param("subjectsId") String subjectsId,
                                                           @Param("yearClassId") String yearClassId,
                                                           @Param("classTypeId") String classTypeId,
                                                           @Param("teacherId") String teacherId,
                                                           @Param("gradeId") String gradeId);

    @Select({"<script>",
            "SELECT",
            "t02.team_id,t02.team_pid,t02.team_name,t02.sourse_id AS teacher_id, ",
            "getStaffNameForId(t02.sourse_id) AS teacher_name, ",
            "SUM( CASE WHEN is_adjust = 1 THEN 1 ELSE 0 END ) AS yet_adjust_count, ",
            "SUM( CASE WHEN is_adjust = 0 AND course_type_id &lt;&gt; class_type_id THEN 1 ELSE 0 END ) AS not_adjust_count ",
            "FROM (",
                "SELECT ",
                "td.*,td.sourse_id as teacher_id ,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
                "FROM ",
                "track_market AS tm ",
                "LEFT JOIN track_detail AS td ON tm.id = td.track_id ",
                "AND tm.deleted = td.deleted ",
                "WHERE ",
                "tm.classify = ${classify} ",
                "AND tm.track_type = ${trackType} ",
                "AND tm.`status` = 1 ",
                "AND tm.deleted = 0 ",
                "AND tm.year_part = ${yearPart} ",
                "AND tm.quarter_num = ${quarterNum} ",
                "<when test='teamId!=null and teamId!=&apos;&apos; '>",
                "and tm.id IN (${teamId}) ",
                "</when>",
                "GROUP BY ",
                "tm.id,td.sourse_id ",
            ") AS t02",
            "LEFT JOIN (",
                "SELECT ",
                "t1.id, ",
                "t1.year_part, ",
                "t1.quarter_num, ",
                "t1.student_id, ",
                "t1.subjects_id, ",
                "t1.class_type_id, ",
                "t3.school_id, ",
                "t3.teacher_id, ",
                "t3.course_type_id, ",
                "t3.year_class_id, ",
                "t1.is_adjust ",
                "FROM ",
                "student_class_type AS t1 ",
                "INNER JOIN student_grade AS t2 ON t1.student_id = t2.student_id ",
                "AND t1.year_part = t2.year_part ",
                "AND t1.quarter_num = t2.quarter_num ",
                "AND t1.subjects_id = t2.subjects_id ",
                "AND t2.lesson_type = 1 ",
                "AND t2.is_last = 1 ",
                "AND t2.deleted = 0 ",
                "INNER JOIN grade AS t3 ON t3.id = t2.grade_id ",
                "AND t1.year_part = t3.year_part ",
                "AND t1.quarter_num = t3.quarter_num ",
                "AND t3.deleted = 0 ",
                "WHERE ",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND is_fit = 1 ",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                    "AND t3.year_class_id in (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                    "AND t3.course_type_id in (${classTypeId}) ",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                    "AND t3.school_id in (${schoolId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                    "AND t3.subjects_id in (${subjectsId}) ",
                "</when>",
            ") AS t ON t.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t.class_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            "GROUP BY t02.team_id ",
            "<if test= 'isDetails == 1 '>",
                ",t02.sourse_id",
                "HAVING t02.sourse_id IS NOT NULL ",
            "</if>",
            "ORDER BY not_adjust_count DESC ",
            "</script>"})
    public abstract List<StudentGrade> adjustGradeTeamList(@Param("yearPart") Integer yearPart,
                                                           @Param("quarterNum") Integer quarterNum,
                                                           @Param("schoolId") String schoolId,
                                                           @Param("yearClassId") String yearClassId,
                                                           @Param("type") Integer type,
                                                           @Param("subjectsId") String subjectsId,
                                                           @Param("classTypeId") String classTypeId,
                                                           @Param("isDetails") Integer isDetails,
                                                           @Param("teamId") String teamId,
                                                           @Param("classify") String classify,
                                                           @Param("trackType") String trackType

    );

    @Select({"<script>",
            "SELECT ",
            "t.* ",
            "FROM (",
                "SELECT td.*,tm.teacher_id,tm.team_name,tm.id AS team_id,tm.pid AS team_pid ",
                "FROM ",
                "track_detail AS td ",
                "INNER JOIN track_market AS tm ON tm.year_part = ${yearPart} ",
                "AND tm.quarter_num = ${quarterNum} ",
                "AND tm.id = td.track_id ",
                "AND tm.deleted = td.deleted ",
                "WHERE ",
                "tm.classify = ${classify} ",
                "AND tm.track_type = ${trackType} ",
                "AND tm.STATUS = 1 ",
                "AND tm.deleted = 0 ",
                "<when test='teamId!=null and teamId!=&apos;&apos;'>",
                "AND tm.id IN (${teamId}) ",
                "</when>",
                "GROUP BY td.track_id,td.sourse_id ",
            ") AS t02",
            "INNER JOIN (",
                "SELECT ",
                "t1.id, ",
                "t1.year_part, ",
                "t1.quarter_num, ",
                "t1.student_id, ",
                "t1.subjects_id, ",
                "t1.class_type_id, ",
                "t3.school_id, ",
                "t3.teacher_id, ",
                "t3.course_type_id, ",
                "t1.is_adjust, ",
                "t2.year_class_id, ",
                "getCourseTypeName(t1.class_type_id) AS class_type_str, ",
                "getCourseTypeName(t3.course_type_id) AS course_type_str, ",
                "getStudentNameForId(t1.student_id) AS student_name ",
                "FROM ",
                "student_class_type AS t1 ",
                "INNER JOIN student_grade AS t2 ON t1.student_id = t2.student_id ",
                "AND t1.year_part = t2.year_part ",
                "AND t1.quarter_num = t2.quarter_num ",
                "AND t1.subjects_id = t2.subjects_id ",
                "AND t2.lesson_type = 1 ",
                "AND t2.is_last = 1 ",
                "AND t2.deleted = 0 ",
                "INNER JOIN grade AS t3 ON t3.id = t2.grade_id ",
                "AND t1.year_part = t3.year_part ",
                "AND t1.quarter_num = t3.quarter_num ",
                "AND t3.deleted = 0 ",
                "WHERE ",
                "t1.year_part = ${yearPart} ",
                "AND t1.quarter_num = ${quarterNum} ",
                "AND is_fit = 1 ",
                "<when test='teacherId!=null and teacherId!=&apos;&apos;'>",
                    "AND t3.teacher_id = ${teacherId} ",
                "</when>",
                "<when test='yearClassId!=null and yearClassId!=&apos;&apos;'>",
                    "AND t3.year_class_id in (${yearClassId}) ",
                "</when>",
                "<when test='classTypeId!=null and classTypeId!=&apos;&apos;'>",
                    "AND t3.course_type_id in (${classTypeId}) ",
                "</when>",
                "<when test='schoolId!=null and schoolId!=&apos;&apos;'>",
                    "AND t3.school_id in (${schoolId}) ",
                "</when>",
                "<when test='gradeId!=null and gradeId!=&apos;&apos;'>",
                    "AND t3.id in (${gradeId}) ",
                "</when>",
                "<when test='subjectsId!=null and subjectsId!=&apos;&apos;'>",
                    "AND t3.subjects_id in (${subjectsId}) ",
                "</when>",
                "<if test= 'detailType == 0 '>",
                    "AND ((t1.is_adjust = 0 AND t1.class_type_id &lt;&gt; t3.course_type_id) OR t1.is_adjust = 1) ",
                "</if>",
                "<if test= 'detailType == 1 '>",
                    "AND t1.is_adjust = 1 ",
                "</if>",
                "<if test= 'detailType == 2 '>",
                    "AND t1.is_adjust = 0 AND t1.class_type_id &lt;&gt; t3.course_type_id ",
                "</if>",
            ") AS t ON t.teacher_id = t02.sourse_id ",
            "AND ( find_in_set( t.year_class_id, t02.year_class_id_str ) OR t02.year_class_id_str = '' ) ",
            "AND ( find_in_set( t.school_id, t02.school_id_str ) OR t02.school_id_str = '' ) ",
            "AND ( find_in_set( t.class_type_id, t02.class_type_id_str ) OR t02.class_type_id_str = '' ) ",
            "WHERE t.id IS NOT NULL ",
            "</script>"})
    public abstract IPage<StudentGrade> adjustGradeTeamDetails(IPage<?> page,
                                                               @Param("yearPart") Integer yearPart,
                                                               @Param("quarterNum") Integer quarterNum,
                                                               @Param("schoolId") String schoolId,
                                                               @Param("detailType") Integer detailType,
                                                               @Param("subjectsId") String subjectsId,
                                                               @Param("yearClassId") String yearClassId,
                                                               @Param("classTypeId") String classTypeId,
                                                               @Param("teacherId") String teacherId,
                                                               @Param("gradeId") String gradeId,
                                                               @Param("teamId") String teamId,
                                                               @Param("classify") String classify,
                                                               @Param("trackType") String trackType);

}
